package com.hncj.yqfk.dao;


import com.hncj.yqfk.entity.CRXX;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface CRXX_Dao extends JpaRepository<CRXX,String> {

    /**
     * 根据时间段和学号来查询学生行为信息
     * @param XH 学号
     * @param stime 开始时间
     * @param etime 结束时间
     * @return 学生行为信息
     */
    @Query(value = "SELECT * FROM V_XGXT_BZB_CRXX WHERE XH=:XH AND SBSJ BETWEEN to_date(:stime,'yyyy-MM-dd hh24:mi:ss') AND to_date(:etime,'yyyy-MM-dd hh24:mi:ss') ORDER BY SBSJ ",nativeQuery = true)
    List<CRXX> GetByDayAndUsername(@Param("XH") String XH, @Param("stime") String stime, @Param("etime") String etime);

    /**
     * 根据时间段，大门，返回小时段人流量
     * @param CRWZ 出入哪个门
     * @param stime 开始时间
     * @param etime 结束时间
     * @return  人流量
     */
    @Query(value = "SELECT to_char(SBSJ,'mm-dd:HH24') AS HOUR,COUNT(1) AS HOUR_JNUM FROM V_XGXT_BZB_CRXX WHERE CRBZ = 1 AND CRRLX = 1 AND CRWZ = :CRWZ AND SBSJ BETWEEN to_date(:stime,'yyyy-MM-dd hh24:mi:ss') AND to_date(:etime,'yyyy-MM-dd hh24:mi:ss') GROUP BY to_char(SBSJ,'mm-dd:HH24') ORDER BY to_char(SBSJ,'mm-dd:HH24')",nativeQuery = true)
    List<Map<String,Object>> GetByNaDa(@Param("CRWZ") String CRWZ, @Param("stime") String stime, @Param("etime") String etime);

    /**
     * 查询大门目前小时段的人流量
     * @param CRWZ 出入哪个门
     * @param start_time
     * @return 人流量
     */
    @Query(value = "SELECT COUNT(1) AS RND FROM V_XGXT_BZB_CRXX WHERE CRBZ = 1 AND CRRLX = 1 AND CRWZ = :CRWZ AND SBSJ > to_date(:start_time,'yyyy-MM-dd hh24:mi:ss')",nativeQuery = true)
    Integer GetByRndDa(@Param("CRWZ") String CRWZ, @Param("start_time") String start_time);

}
